﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[vwMSX_todo_list]
#-- Purpose:		List of SQL Servers that need changes to meet standards
#--	Last Update:	04/28/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE VIEW [dbo].[vwMSX_todo_list]
AS

SELECT	(upgrade_version + change_sql_service + change_agent_service) change_needed,
		sql_version,
		upgrade_version,
		change_sql_service,
		change_agent_service,
		ssd_sql_service,
		ssd_agent_service,
		ssd_sqlserver_name_ro,
		sql_description,
		ssd_type_ro,
		sql_edition_ro,
		change_edition
FROM	(
		SELECT		CAST(sql_version_major_ro as varchar(5)) + '.' + CAST(sql_version_minor_ro as varchar(5)) sql_version,
					(CASE	WHEN sql_version_major_ro = 8 THEN 1 ELSE 0 END) upgrade_version,
					(CASE	WHEN ssd_sql_service = 'MFBNTDOM\Administrator' THEN 1
							WHEN ssd_sql_service = 'MFBNTDOM\dzixmss' THEN 1
							WHEN ssd_sql_service NOT LIKE 'MFBNTDOM\%' THEN 1
							WHEN LTRIM(RTRIM(ISNULL(ssd_sql_service, ''))) = '' THEN 1
							ELSE 0 END) change_sql_service,
					(CASE	WHEN ssd_agent_service = 'MFBNTDOM\Administrator' THEN 1
							WHEN ssd_agent_service = 'MFBNTDOM\dzixmss' THEN 1
							WHEN ssd_agent_service NOT LIKE 'MFBNTDOM\%' THEN 1
							WHEN LTRIM(RTRIM(ISNULL(ssd_agent_service, ''))) = '' THEN 1
							ELSE 0 END) change_agent_service,
					ssd_sql_service,
					ssd_agent_service,
					ssd_sqlserver_name_ro,
					app.[app_name] sql_description, 
					ssd_type_ro,
					sql_edition_ro,
					(CASE	WHEN ssd_type_ro IN ('Test', 'QA') AND sql_edition_ro NOT IN ('Developer', 'Express') THEN 1
							ELSE 0 END) change_edition
		FROM		[dbo].[tblMSX_server_discovery] ssd
		JOIN		[dbo].[tblMSX_server_sql_info] sql ON ssd.ssd_id = sql.sql_ssd_id
		LEFT JOIN	[dbo].[tblMSX_application] app ON ssd.[ssd_app_id] = app.[app_id]
		WHERE		ssd_is_disabled = 0
					AND ssd_is_vendor_managed_ro = 0
					AND ssd_has_access_ro = 1
					AND ssd_sqlserver_name_ro NOT LIKE '(local)%'
		) t1